package DAO;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.ClassModel;
import model.Schedule;
import model.Staff;
import model.Subject;
import util.DataUtil;

public class ScheduleDAO {

    public static List<Schedule> getAll(String ClassID) {
        List<Schedule> list = new ArrayList<Schedule>();
        String sql = "select DISTINCT Schedule.DateSchedule,Schedule.ClassID,Class.ClassName from Schedule inner join Class on Schedule.ClassID = Class.ClassID where Schedule.ClassID = ?";
        try {
            PreparedStatement pst = DataUtil.getConnection().prepareCall(sql);
            pst.setString(1, ClassID);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Schedule sc = new Schedule();
                ClassModel clm = new ClassModel();
                clm.setClassId(rs.getString("ClassID"));
                clm.setClassName(rs.getString("ClassName"));
                sc.setDate(rs.getString("DateSchedule"));
                sc.setClassModel(clm);
                list.add(sc);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean addSchedule(List<Schedule> list) {
        int kq = 0;
        int[] ck = new int[list.size()];
        String call = "{Call ADD_SCHEDULE(?,?,?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            for (Schedule sc : list) {
                System.out.println(sc.getSubject() + "---" + sc.getSession() + "---" + sc.getStaff());
                cs.setString(1, sc.getClassModel().getClassId());
                cs.setString(2, sc.getSubject().getSubjectId());
                cs.setInt(3, sc.getSession());
                cs.setString(4, sc.getStartTime());
                cs.setString(5, sc.getEndTime());
                cs.setString(6, sc.getDate());
                cs.setString(7, sc.getStaff().getStaffId());
                cs.addBatch();
            }
            cs.executeBatch();
            for (int i = 0; i < ck.length; i++) {
                if (ck[i] < 1) {
                    kq = 1;
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (kq > 0) ? true : false;
    }

    public static boolean testadd(List<Schedule> list) {
        int kq = 1;
        String call = "{Call ADD_SCHEDULE(?,?,?,?,?,?,?)}";
        for (Schedule sc : list) {
            try {
                CallableStatement cs = DataUtil.getConnection().prepareCall(call);
                cs.setString(1, sc.getClassModel().getClassId());
                cs.setString(2, sc.getSubject().getSubjectId());
                cs.setInt(3, sc.getSession());
                cs.setString(4, sc.getStartTime());
                cs.setString(5, sc.getEndTime());
                cs.setString(6, sc.getDate());
                cs.setString(7, sc.getStaff().getStaffId());
                cs.executeUpdate();
            } catch (SQLException e) {
                kq = 0;
                e.printStackTrace();
            }
        }
        return (kq > 0) ? true : false;
    }

    public static List<Schedule> getScheduleByDate(String ClassID, String Date) {
        List<Schedule> list = new ArrayList<Schedule>();
        String sql = "select * from Subject inner join Schedule on Subject.SubjectID = Schedule.SubjectID \n"
                + "inner join Class on Schedule.ClassID = Class.ClassID\n"
                + "inner join Staff on Schedule.StaffID = Staff.StaffID where Schedule.ClassID = ? and Schedule.DateSchedule = ?";
        try {
            PreparedStatement pst = DataUtil.getConnection().prepareCall(sql);
            pst.setString(1, ClassID);
            pst.setString(2, Date);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                ClassModel clm = new ClassModel();
                clm.setClassId(rs.getString("ClassID"));
                clm.setClassName(rs.getString("ClassName"));

                Subject sbj = new Subject();
                sbj.setSubjectId(rs.getString("SubjectID"));
                sbj.setSubjectName(rs.getString("SubName"));
                sbj.setNoSession(rs.getInt("NoOfSession"));

                int session = rs.getInt("Session");
                String startTime = rs.getString("StartTime");
                String endTime = rs.getString("endTime");
                String date = rs.getString("DateSchedule");

                Staff st = new Staff();
                st.setStaffId(rs.getString("StaffID"));
                st.setUserName(rs.getString("Username"));
                st.setFullName(rs.getString("FullName"));
                Schedule sc = new Schedule(clm, sbj, session, startTime, endTime, date, st);
                list.add(sc);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean checkScheduleByClassID(String classid) {
        boolean fl = false;
        String sql = "select * from Schedule where ClassID = ?";
        try {
            PreparedStatement pst = DataUtil.getConnection().prepareCall(sql);
            pst.setString(1, classid);
            ResultSet rss = pst.executeQuery();
            if (rss.next()) {
                fl = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean checkScheduleByDate(String classID, String date) {
        boolean fl = false;
        String sql = "select * from Schedule where ClassID = ? and DateSchedule = ?";
        try {
            PreparedStatement pst = DataUtil.getConnection().prepareCall(sql);
            pst.setString(1, classID);
            pst.setString(2, date);
            ResultSet rss = pst.executeQuery();
            if (rss.next()) {
                fl = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean editSchedule(Schedule sc) {
        int rs = 0;
        String call = "update Schedule set StartTime = ?,EndTime = ?,DateSchedule = ?,StaffID = ? where ClassID = ? and SubjectID = ? and Session = ?";
        try {
            PreparedStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(5, sc.getClassModel().getClassId());
            cs.setString(6, sc.getSubject().getSubjectId());
            cs.setInt(7, sc.getSession());
            cs.setString(1, sc.getStartTime());
            cs.setString(2, sc.getEndTime());
            cs.setString(3, sc.getDate());
            cs.setString(4, sc.getStaff().getStaffId());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return (rs > 0) ? true : false;
    }
}
